package com.yeban.test1.preparestatement;

import com.yeban.test1.entity.Student;
import com.yeban.test1.utils.C3P0ByThreadLocalUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDao1 {

    // 1.统计学生人数
    public void selectNums() {
        //获取连接对象
        // Connection conn = Test1Utils.getConn();
        Connection conn = C3P0ByThreadLocalUtils.getConn();
        String sql = "select count(*) from stus";
        try (PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            if (rs.next()) {
                System.out.println("学生人数共计：" + rs.getInt(1) + " 人");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //6.关闭资源
            C3P0ByThreadLocalUtils.close();
        }
    }

    // 2. 查询全部学生
    public List<Student> selectAll() {
        // Connection conn = Test1Utils.getConn();
        Connection conn = C3P0ByThreadLocalUtils.getConn();
        String sql = "select * from stus";

        List<Student> list = new ArrayList<>();

        try (PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                Student stu = new Student();
                stu.setId(rs.getString("id"));
                stu.setName(rs.getString("name"));
                stu.setGender(rs.getString("gender"));
                stu.setGrade(rs.getString("grade"));
                stu.setPhone(rs.getString("phone"));
                stu.setEmail(rs.getString("email"));

                stu.setBirthday(rs.getDate("birthday").toLocalDate());
                list.add(stu);
            }

        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //6.关闭资源
            C3P0ByThreadLocalUtils.close();
        }
        return list;
    }

    // 3. 按学号查询
    public Student selectOneById(String id) {
        // Connection conn = Test1Utils.getConn();
        Connection conn = C3P0ByThreadLocalUtils.getConn();
        String sql = "select * from stus where id=?";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, id);
            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                Student stu = new Student();
                stu.setId(rs.getString("id"));
                stu.setName(rs.getString("name"));
                stu.setGender(rs.getString("gender"));
                stu.setGrade(rs.getString("grade"));
                stu.setPhone(rs.getString("phone"));
                stu.setEmail(rs.getString("email"));
                stu.setBirthday(rs.getDate("birthday").toLocalDate());
                return stu;
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //6.关闭资源
            C3P0ByThreadLocalUtils.close();
        }
        return null;
    }

    // 4. 按姓名模糊查询
    public List<Student> selectByName(String name) {
        // Connection conn = Test1Utils.getConn();
        Connection conn = C3P0ByThreadLocalUtils.getConn();
        String sql = "select * from stus where name like concat('%',?,'%')";

        List<Student> list = new ArrayList<>();

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Student stu = new Student();
                stu.setId(rs.getString("id"));
                stu.setName(rs.getString("name"));
                stu.setGender(rs.getString("gender"));
                stu.setGrade(rs.getString("grade"));
                stu.setPhone(rs.getString("phone"));
                stu.setEmail(rs.getString("email"));
                stu.setBirthday(rs.getDate("birthday").toLocalDate());
                list.add(stu);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //6.关闭资源
            C3P0ByThreadLocalUtils.close();
        }
        return list;
    }

    // 5. 修改生日
    public void updateBirthday(String id, Date birthday) {
        // Connection conn = Test1Utils.getConn();
        Connection conn = C3P0ByThreadLocalUtils.getConn();
        String sql = "update stus set birthday=? where id=?";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setDate(1, birthday);
            ps.setString(2, id);
            int i = ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //6.关闭资源
            C3P0ByThreadLocalUtils.close();
        }
    }

    // 6. 删除学生
    public void deleteStu(String id) {
        // Connection conn = Test1Utils.getConn();
        Connection conn = C3P0ByThreadLocalUtils.getConn();
        String sql = "delete from stus where id=?";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, id);
            int i = ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //6.关闭资源
            C3P0ByThreadLocalUtils.close();
        }
    }
}
